package com.github.springbootPlus.excel.parsing;

import com.github.springbootPlus.excel.ExcelDefinitionReader;
import com.github.springbootPlus.excel.config.ExcelDefinition;
import com.github.springbootPlus.excel.config.FieldValue;
import com.github.springbootPlus.excel.exception.ExcelException;
import com.github.springbootPlus.excel.result.ExcelImportResult;
import com.github.springbootPlus.excel.util.ExcelUtil;
import com.github.springbootPlus.excel.util.ReflectUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Excel导入实现类
 *
 * @author lisuo
 */
public class ExcelImport extends AbstractExcelResolver {
    /**
     * Logger for this class
     */
    private static final Logger logger = LoggerFactory.getLogger(ExcelImport.class);


    public ExcelImport(ExcelDefinitionReader definitionReader) {
        super(definitionReader);
    }

    /**
     * 读取Excel信息
     *
     * @param id            注册的ID
     * @param excelStream   Excel文件流
     * @param sheetIndex    Sheet索引位置
     * @param multivalidate 是否逐条校验，默认单行出错立即抛出ExcelException，为true时为批量校验,可通过ExcelImportResult.hasErrors,和getErrors获取具体错误信息
     * @return
     * @throws Exception
     */
    public ExcelImportResult readExcel(String id, InputStream excelStream, Integer sheetIndex, boolean multivalidate) throws Exception {
        //从注册信息中获取Bean信息
        ExcelDefinition excelDefinition = definitionReader.getRegistry().get(id);
        if (excelDefinition == null) {
            throw new ExcelException("没有找到 [" + id + "] 的配置信息");
        }
        try {
            return doReadExcel(excelDefinition, excelStream, sheetIndex, multivalidate);
        } finally {
            ExcelContextHolder.removeContext();
        }
    }

    protected ExcelImportResult doReadExcel(ExcelDefinition excelDefinition, InputStream excelStream, Integer sheetIndex, boolean multivalidate) throws Exception {
        Workbook workbook = WorkbookFactory.create(excelStream);
        ExcelImportResult result = new ExcelImportResult();
        int titleIndex = excelDefinition.getTitleIndex();
        result.setTitleIndex(titleIndex);
        //读取sheet,sheetIndex参数优先级大于ExcelDefinition配置sheetIndex
        Sheet sheet = workbook.getSheetAt(sheetIndex == null ? excelDefinition.getSheetIndex() : sheetIndex);
        //标题之前的数据处理
        List<List<Object>> header = readHeader(excelDefinition, sheet);
        result.setHeader(header);
        //获取标题
        List<String> titles = readTitle(excelDefinition, sheet, titleIndex);
        result.setTitle(titles);
        //模板是否被篡改过
        checkTitle(result.getErrors(), excelDefinition, titles, titleIndex);
        //获取Bean
        List<Object> listBean = readRows(result.getErrors(), excelDefinition, titles, sheet, titleIndex, multivalidate);
        result.setListBean(listBean);
        //行数据唯一性判断
        String uniqueFields = excelDefinition.getUniqueFields();
        String uniqueDesc = excelDefinition.getUniqueDesc();
        if (StringUtils.isNotBlank(uniqueFields) && result.hasData()) {
            String[] uniqueFieldList = StringUtils.split(uniqueFields, ",");
            ExcelUtil.uniqueCheck(uniqueFieldList, uniqueDesc, result, titleIndex + 1);
        }
        return result;
    }


    /**
     * 解析标题之前的内容,如果ExcelDefinition中titleIndex 不是0
     *
     * @param excelDefinition
     * @param sheet
     * @return
     */
    protected List<List<Object>> readHeader(ExcelDefinition excelDefinition, Sheet sheet) {
        List<List<Object>> header = null;
        int titleIndex = excelDefinition.getTitleIndex();
        if (titleIndex != 0) {
            header = new ArrayList<List<Object>>(titleIndex);
            for (int i = 0; i < titleIndex; i++) {
                Row row = sheet.getRow(i);
                short cellNum = row.getLastCellNum();
                List<Object> item = new ArrayList<Object>(cellNum);
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    Object value = getCellValue(cell);
                    item.add(value);
                }
                header.add(item);
            }
        }
        return header;
    }


    /**
     * 读取多行
     *
     * @param excelDefinition
     * @param titles
     * @param sheet
     * @param titleIndex
     * @return
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    protected <T> List<T> readRows(List<ExcelError> errors, ExcelDefinition excelDefinition, List<String> titles, Sheet sheet, int titleIndex, boolean multivalidate) throws Exception {
        int rowNum = sheet.getLastRowNum();
        //读取数据的总共次数
        int totalNum = rowNum - titleIndex;
        int startRow = 1;
        List<T> listBean = new ArrayList<T>(totalNum);
        for (int i = titleIndex + 1; i <= rowNum; i++) {
            Row row = sheet.getRow(i);
            Object bean = readRow(errors, excelDefinition, row, titles, startRow + i, multivalidate);
            listBean.add((T) bean);
        }
        return listBean;
    }

    /**
     * 读取1行
     *
     * @param excelDefinition
     * @param row
     * @param titles
     * @param rowNum          第几行
     * @return
     * @throws Exception
     */
    protected Object readRow(List<ExcelError> errors, ExcelDefinition excelDefinition, Row row, List<String> titles, int rowNum, boolean multivalidate) throws Exception {
        //创建注册时配置的bean类型
        Object bean = ReflectUtils.newInstance(excelDefinition.getClazz());
        boolean rowValid = true;
        // 1.
        for (FieldValue fieldValue : excelDefinition.getFieldValues()) {
            String title = fieldValue.getTitle().trim();
            for (int j = 0; j < titles.size(); j++) {
                if (title.equals(titles.get(j))) {
                    try {
                        Cell cell = row.getCell(j);
                        //获取Excel原生value值
                        Object value = getCellValue(cell);
                        //校验
                        validate(fieldValue, value, rowNum);
                        //非空非空格
                        if (StringUtils.isNotBlank(ObjectUtils.toString(value))) {
                            if (value instanceof String) {
                                //去除前后空格
                                value = value.toString().trim();
                            }
                            value = super.convert(bean, value, fieldValue, Type.IMPORT, rowNum);
                            ReflectUtils.setProperty(bean, fieldValue.getName(), value);
                        }
                        break;
                    } catch (ExcelException e) {
                        rowValid = false;
                        //应用multivalidate
                        if (multivalidate) {
                            errors.add(new ExcelError(rowNum, e.getMessage()));
                            continue;
                        } else {
                            throw e;
                        }
                    }
                }
            }
        }

        // 2. 设置默认值
        Map<String, Object> context = ExcelContextHolder.getContext();
        for (FieldValue fieldValue : excelDefinition.getFieldValues()) {
            //
            String defaultValue = fieldValue.getDefaultValue();
//			String convName = fieldValue.getCellValueConverterName();
            Object val = ReflectUtils.getProperty(bean, fieldValue.getName());

            if (StringUtils.isBlank(ObjectUtils.toString(val)) && defaultValue != null) {
                Object value = defaultValue;
                if (defaultValue.startsWith("${") && defaultValue.endsWith("}")) {
                    String key = defaultValue.substring(2, defaultValue.length() - 1);
                    value = (context == null) ? null : context.get(key);
                }
                if (defaultValue.equals("newDate()")) {
                    value = new Date();
                }
                ReflectUtils.setProperty(bean, fieldValue.getName(), value);
            }
        }

        // 3. ROWFILTER自定义数据校验
        String rowFilter = excelDefinition.getRowFilter();
        if (StringUtils.isNotBlank(rowFilter) && rowValid)
            checkRow(rowFilter, bean, RowFilter.Type.IMPORT, rowNum, errors);
        return bean;
    }

    protected List<String> readTitle(ExcelDefinition excelDefinition, Sheet sheet, int titleIndex) {
        // 获取Excel标题数据
        Row hssfRowTitle = sheet.getRow(titleIndex);
        int cellNum = hssfRowTitle.getLastCellNum();
        List<String> titles = new ArrayList<String>(cellNum);
        // 获取标题数据
        for (int i = 0; i < cellNum; i++) {
            Cell cell = hssfRowTitle.getCell(i);
            Object value = getCellValue(cell);
            if (value == null) {
                logger.warn("id 为:[" + excelDefinition.getId() + "] " + (i + 1) + "列的标题不能为[ null ]");
                value = "";
            }
            titles.add(value.toString().trim());
        }
        return titles;
    }

    /**
     * 检查导入模板是否被修改过,一些必填字段列必须出现在导入模板中
     *
     * @param errors
     * @param excelDefinition
     * @param titles
     * @param titleIndex
     */
    void checkTitle(List<ExcelError> errors, ExcelDefinition excelDefinition, List<String> titles, int titleIndex) {
        for (FieldValue fieldValue : excelDefinition.getFieldValues()) {
            if (!fieldValue.isNull()) {
                String title = fieldValue.getTitle().trim();
                boolean hasTitle = false;
                for (int j = 0; j < titles.size(); j++) {
                    if (title.equals(titles.get(j))) {
                        hasTitle = true;
                        break;
                    }
                }
                if (!hasTitle)
                    errors.add(new ExcelError(titleIndex + 1, "列[" + title + "]不存在,请使用正确的导入模板"));
            }
        }
    }

    /**
     * 数据有效性校验
     *
     * @param fieldValue
     * @param value
     * @param rowNum
     */
    private void validate(FieldValue fieldValue, Object value, int rowNum) {
        if (value == null || StringUtils.isBlank(value.toString())) {
            //空校验
            if (!fieldValue.isNull()) {
                String err = getErrorMsg(fieldValue, "不能为空", rowNum);
                throw new ExcelException(err);
            }
        } else {
            //正则校验
            String regex = fieldValue.getRegex();
            if (StringUtils.isNotBlank(regex)) {
                String val = value.toString().trim();
                if (!val.matches(regex)) {
                    String errMsg = fieldValue.getRegexErrMsg() == null ? "格式错误" : fieldValue.getRegexErrMsg();
                    String err = getErrorMsg(fieldValue, errMsg, rowNum);
                    throw new ExcelException(err);
                }
            }
        }
    }


}
